﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_ExtendIQ : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        public List<string> rzcode_list;
                
        public frm_ExtendIQ()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.rzcode_list = new List<string>();

            LoadData();            
        }


        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT * FROM [241_ExportCheckEx] ORDER BY RZCode, IQID";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridExtendIQ.DataSource = sourceDataSet.Tables[0];
            }
            else
            {                
                DataTable newTable = new DataTable();
                newTable.Columns.Add("RZCode", typeof(string));
                newTable.Columns.Add("IQID", typeof(string));
                newTable.Columns.Add("Quantity", typeof(float));
                gridExtendIQ.DataSource = newTable;                
            }
            ExtendIQ.Columns[0].Caption = "RZ Code";
            ExtendIQ.Columns[1].Caption = "IQ ID";
            ExtendIQ.Columns[2].Caption = "Quantity";
            ExtendIQ.BestFitColumns();
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From Excel
        ////////////////////////////////////

        private void importDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {                
                // Import new data from Excel
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [RZCode], [IQID], [Quantity] from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Get data error: " + ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {
                DataSet exportIQ = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable exportIQ_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [241_ExportCheckEx]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(exportIQ, "241_ExportCheckEx");
                    exportIQ_local = exportIQ.Tables["241_ExportCheckEx"];

                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        if (!string.IsNullOrEmpty(excel_row["RZCode"].ToString().Trim()) && (!string.IsNullOrEmpty(excel_row["IQID"].ToString().Trim())))
                        {
                            DataRow new_row = exportIQ_local.NewRow();
                            new_row["RZCode"] = excel_row["RZCode"].ToString().Trim();
                            new_row["IQID"] = excel_row["IQID"].ToString().Trim();
                            float qty = 0;
                            float.TryParse(excel_row["Quantity"].ToString().Trim(), out qty);
                            new_row["Quantity"] = qty;
                            exportIQ_local.Rows.Add(new_row);
                        }
                    }

                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(exportIQ, "241_ExportCheckEx");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();                    

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Import Error: " + ex.Message);
                }
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                int[] selected_row = ExtendIQ.GetSelectedRows();
                myConnection.Open();
                int k = 0;
                foreach (int i in selected_row)
                {
                    bool exist = false;
                    try
                    {
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT * FROM [241_ExportCheckEx] WHERE"
                            + " RZCode = '" + ExtendIQ.GetRowCellValue(i, "RZCode").ToString() + "'"
                            + " AND IQID = '" + ExtendIQ.GetRowCellValue(i, "IQID").ToString() + "'";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            exist = true;
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                    }
                    catch
                    {
                        exist = false;
                    }
                    if (exist)
                    {
                        try
                        {
                            string sql = "DELETE FROM [241_ExportCheckEx]"
                                + " WHERE RZCode='" + ExtendIQ.GetRowCellValue(i, "RZCode").ToString() + "' AND"
                                + " IQID = '" + ExtendIQ.GetRowCellValue(i, "IQID").ToString() + "'";
                            SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                            cmd_sql.ExecuteNonQuery();
                            cmd_sql.Dispose();
                            k++;
                        }
                        catch
                        {

                        }
                    }
                }
                myConnection.Close();
                MessageBox.Show(k + " recoreds have been deleted.");
                LoadData();
            }
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_ExtendIQ add_form = new frm_Add_ExtendIQ();
            add_form.rzcode_list = this.rzcode_list;
            if (add_form.CreateForm())
            {
                add_form.ShowDialog();
                LoadData();
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }
    }
}
